﻿using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.OleDb;
using System.Text;

namespace PAS.Helper
{
    public abstract class AbstractImportor
    {
        public const string DATA_LOCATION_MEMO = "数据位置",
                            ERR_MSG_MEMO = "错误描述";

        public DataTable ErrMessage;
        protected StringCollection m_ErrDataLocatoins = new StringCollection();

        protected string m_ExcelPath;
        protected string m_KeyColName;

        protected StringCollection m_RightDataLocatoins = new StringCollection();
        protected string m_SheetName;

        protected Hashtable m_SourceData;

        public StringCollection ErrDateLocations
        {
            get { return m_ErrDataLocatoins; }
        }

        public StringCollection RightDataLocations
        {
            get { return m_RightDataLocatoins; }
        }


        protected AbstractImportor(string excelPah, string sheetName)
        {

            m_ExcelPath = excelPah;
            m_SheetName = sheetName;

            if(ErrMessage==null)
            {
                ErrMessage = new DataTable();
                ErrMessage.Columns.Add(DATA_LOCATION_MEMO, typeof (string));
                ErrMessage.Columns.Add(ERR_MSG_MEMO, typeof(string));
            }
        }

        protected void SetErrMessage(string dataLocation, string errMsg)
        {
            DataRow[] rs = ErrMessage.Select(DATA_LOCATION_MEMO + "='" + dataLocation + "'");
            if (rs.Length > 0)
            {
                string msgVal = Convert.ToString(rs[0][ERR_MSG_MEMO]);
                rs[0][ERR_MSG_MEMO] = msgVal + "<br>" + errMsg;
            }
            else
            {
                ErrMessage.Rows.Add(new object[] { dataLocation, errMsg });
                m_ErrDataLocatoins.Add(dataLocation);
            }
        }

        protected virtual void InitSourceData()
        {
            if (m_SourceData == null) m_SourceData = new Hashtable();
            InitSourceDataBySheetName(m_SheetName);
        }

        private void InitSourceDataBySheetName(string sheetName)
        {
            string cn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + m_ExcelPath +
                        ";Extended Properties=Excel 8.0;";
            var dt = new DataTable();
            //从指定的表明查询数据,可先把所有表明列出来供用户选择
            string strSql = "select * from [" + sheetName + "$]";
            var da = new OleDbDataAdapter(strSql, cn);
            var htTemp = new Hashtable();
            try
            {
                da.Fill(dt);
                if (dt.Rows.Count == 0)
                    return;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    int dataRowIndex = (i + 2);
                    DataRow dr = dt.Rows[i];
                    string keyVal = Convert.ToString(dr[m_KeyColName]);

                    if (!htTemp.Contains(keyVal))
                    {
                        htTemp.Add(keyVal, i + 2);
                        m_SourceData.Add(dataRowIndex.ToString(), dr);
                    }
                    else
                    {
                        int rowNum = Convert.ToInt32(htTemp[keyVal]);
                        SetErrMessage(dataRowIndex.ToString(),
                                      m_KeyColName + "[" + keyVal + "]不能重复,在第(" + rowNum + ")行已经出现过");
                    }
                }
            }
            catch(Exception ex)
            {
                SetErrMessage("文件有误", "Excel文件中找不到工作表["+m_SheetName+"]");
            }
            finally
            {
                da.Dispose();
                da = null;
            }
        }

        protected virtual void GetObjects()
        {
        }

        protected virtual void CheckEachRowDataFormat(string key,DataRow rowData){}
        protected virtual void CheckRowDataByDbObject(string key, DataRow rowData) { }

        public void CheckData()
        {
            if (m_SourceData == null)
                InitSourceData();
            if (m_SourceData != null)
            {
                foreach (string key in m_SourceData.Keys)
                {
                    var rowData = ((DataRow)m_SourceData[key]);
                    CheckEachRowDataFormat(key, rowData);
                }
                GetObjects();
                foreach (string key in m_SourceData.Keys)
                {
                    var rowData = ((DataRow)m_SourceData[key]);
                    CheckRowDataByDbObject(key, rowData);
                }
            }
        }

        protected virtual string CreateInsertSql(DataRow dataRow)
        {
            return "";
        }

        public void SaveRightData()
        {
            var conn = new OleDbConnection(DataConnHelper.ConnectionString);
            var comm = new OleDbCommand();
            comm.Connection = conn;
            comm.CommandType = CommandType.Text;
            comm.CommandTimeout = 900;
            
            OleDbTransaction tran = null;
            try
            {
                conn.Open();
                comm.Transaction = tran = conn.BeginTransaction();
                var sbInsertSql = new StringBuilder();
                int amount = 0;
                for (int i = 0; i < m_RightDataLocatoins.Count; i++)
                {
                    string strLocation = m_RightDataLocatoins[i];
                    amount++;
                    var rowData = ((DataRow)m_SourceData[strLocation]);
                    string insertSql = CreateInsertSql(rowData);
                    //sbInsertSql.Append(insertSql + "\r");
                    //if (amount % 50 == 0 || i == (m_RightDataLocatoins.Count - 1))
                    //{
                    //    comm.CommandText = sbInsertSql.ToString();
                    //    comm.ExecuteNonQuery();
                    //    sbInsertSql = new StringBuilder();
                    //}
                    comm.CommandText = insertSql;
                    int exceRowAmount = comm.ExecuteNonQuery();
                }
                tran.Commit();
            }
            catch(Exception ex)
            {
                if (tran != null)
                    tran.Rollback();
                throw ex;
            }
            finally
            {
                comm.Dispose();
                conn.Close();
                conn.Dispose();
                comm = null;
                conn = null;
            }
        }
    }
}